package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpActivity;
@Repository
public interface ActivityDao extends PagingAndSortingRepository<TbSpActivity, Integer>, JpaSpecificationExecutor<TbSpActivity>{

	

	@Transactional
	@Modifying
	@Query("update TbSpActivity set sumBudget = sumBudget + :budget,balanceBudget = balanceBudget + :budget,status=:status, hasRemind='0' where id = :id")
	int addBudget(@Param("id")Integer id, @Param("budget")Double budget,@Param("status")String status);

	
	@Transactional
	@Modifying
	@Query("update TbSpActivity set balanceBudget = balanceBudget + :budget where id = :id")
	int reduceBudget(@Param("id")Integer id, @Param("budget")Double budget);

	@Transactional
	@Modifying
	@Query("update TbSpActivity set status = ?1 where id = ?2")
	void updateStatus(String fALSE, Integer id);
	
	
	/**
	 * 查询有效活动
	 * @param branchNo
	 * @return
	 */
	@Query(" from TbSpActivity where  (balanceBudget >0 or activityType='3' ) and status in('1','3','4' ) and  actualStartTime <= SYSDATE() and actualEndTime is null and  find_in_set(?1,branchCode)<>0 ")
	List<TbSpActivity>  findByBranchNo(String branchNo);
	
	/**
	 * 活动预警查询
	 */
	@Query(" from TbSpActivity where balanceBudget<=warnBalance  and  status in('1','3','4' ) and actualStartTime >= SYSDATE() and actualEndTime is null ")
	List<TbSpActivity>  findWarningActivity();

	/**
	 * 活动未及时审批查询
	 */
	@Query(" from TbSpActivity where  status ='2' and planEndTime <= date_format(SYSDATE(),'%Y-%m-%d' ) and actualStartTime is null ")
	List<TbSpActivity> findExpireNotApprove();
	
	/**
	 * 活动到期结束
	 */
	@Query(" from TbSpActivity where  status in('1','3','4' ) and planEndTime <= date_format(SYSDATE(),'%Y-%m-%d' ) and actualStartTime is not null ")
	List<TbSpActivity> findOverActivity();

	@Query(value = "select count(*) from tb_sp_activity ", nativeQuery = true)
	Integer findTotalActivity();

	@Query(" from TbSpActivity where  status in('1','3','4' ) and actualEndTime is null and actualStartTime is not null and activityCode=?1  ")
	List<TbSpActivity> findByActivityCode(String activityCode);

	@Transactional
	@Modifying
	@Query("update TbSpActivity set sumBudget = sumBudget + :budget,balanceBudget = balanceBudget + :budget ,drawTimes=drawTimes+ :drawTimes,drawBalance = drawBalance+ :drawTimes ,specialTimes = specialTimes+ :specialTimes,status=:status, hasRemind='0' where id = :id")
	int updateBudgetAndTimes(@Param("id")Integer id,@Param("budget")Double budget, @Param("drawTimes")Integer drawTimes, @Param("specialTimes")int specialTimes,@Param("status")String status);
}
